/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.istt.dao.FileCapGPXDDao;
import org.istt.domain.FileCapPhepXayDung;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * @author PDQUYNH
 * 
 */
@Repository("fileCapGPXDDao")
public class FileCapGPXDDaoHibernate extends
		BaseDaoHibernate<FileCapPhepXayDung, Long> implements FileCapGPXDDao {
	@Autowired
	public FileCapGPXDDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}
	
	/*
	 * 
	 * 
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<List<Object>> findAllObjects(final Date fromDate, final Date toDate, final Boolean received) {
		return (List<List<Object>>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Date newDate = toDate;
				if (toDate != null) {
					Calendar c = Calendar.getInstance();
					c.setTime(toDate);
					c.add(Calendar.DATE, 1);
					newDate = c.getTime();
				}



				StringBuilder builder = new StringBuilder();
				builder.append("SELECT  t1.ID AS id, t1.THU_TUC AS thutuc, t1.NGAY_NOP as ngaynop, 1 as type ");
				builder.append(" FROM TBL_FILE_CAP_PHEP_XD t1 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t1.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t1.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t1.TIEP_NHAN = :received ");
				}
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t2.ID, t2.THU_TUC, t2.NGAY_NOP, 2 as type  FROM TBL_FILE_CAP_CCHN t2 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t2.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t2.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t2.TIEP_NHAN = :received ");
				}				
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t3.ID, t3.THU_TUC, t3.NGAY_NOP, 3 as type  FROM TBL_FILE_CAP_LAI_CCHN t3 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t3.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t3.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t3.TIEP_NHAN = :received ");
				}				
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t4.ID, t4.THU_TUC, t4.NGAY_NOP, 4 as type  FROM TBL_FILE_DIEU_CHINH_GPXD t4 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t4.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t4.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t4.TIEP_NHAN = :received ");
				}				
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t5.ID, t5.THU_TUC, t5.NGAY_NOP, 5 as type  FROM TBL_FILE_GIA_HAN_CPXD t5 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t5.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t5.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t5.TIEP_NHAN = :received ");
				}				
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t6.ID, t6.THU_TUC, t6.NGAY_NOP, 6 as type  FROM TBL_FILE_BO_SUNG_CCHN t6 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t6.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t6.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t6.TIEP_NHAN = :received ");
				}				
				builder.append(" UNION ALL ");
				builder.append(" SELECT  t7.ID, t7.THU_TUC, t7.NGAY_NOP, 7 as type  FROM TBL_FILE_CAP_CCMGBDS t7 ");
				builder.append(" WHERE (1=1) ");
				if (newDate != null) {
					builder.append(" AND t7.NGAY_NOP <= :toDate ");
				}
				if (fromDate != null) {
					builder.append(" AND t7.NGAY_NOP >= :fromDate ");
				}
				if (received != null) {
					builder.append(" AND t7.TIEP_NHAN = :received ");
				}
				
				@SuppressWarnings("deprecation")
				SQLQuery query = session.createSQLQuery(builder.toString())
				.addScalar("id", Hibernate.LONG)
				.addScalar("thutuc", Hibernate.STRING)
				.addScalar("ngaynop", Hibernate.DATE)
				.addScalar("type", Hibernate.INTEGER)
				;
				if (newDate != null) {
					query.setDate("toDate", newDate);
				}
				if (fromDate != null) {
					query.setDate("fromDate", fromDate);
				}
				if (received != null) {
					query.setBoolean("received", received);
				}				
				return query.list();
			}
		});
	}

	@Override
	public void removeAllFile() {
		hibernateTemplate.bulkUpdate("delete from fileAttachment");
		hibernateTemplate.bulkUpdate("delete from fileBoSungCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapCCMGBDS");
		hibernateTemplate.bulkUpdate("delete from fileCapLaiCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapPhepXayDung");
		hibernateTemplate.bulkUpdate("delete from fileDieuChinhGPXD");
		hibernateTemplate.bulkUpdate("delete from fileGiaHanCPXD");
	}
	
}
